﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace BanHangCshap.Class
{
    class Database
    {
        //Them 1 record su dung SqlParameter
        public bool add(string table, string[] field, object[] obj)
        {
            bool ok = false;
            try
            {
                string sfield = "";
                string sfield1 = "";
                for (int i = 0; i < field.Length; i++)
                {
                    sfield = sfield + field[i] + ",";
                    sfield1 = sfield1 + "@" + field[i] + ",";

                }

                sfield = sfield.Substring(0, sfield.Length - 1);
                sfield1 = sfield1.Substring(0, sfield1.Length - 1);
                string sql = "insert into " + table + "(" + sfield + ") values (" + sfield1 + ")";
                SqlCommand cmd = new SqlCommand(sql, DatabaseStatic.conn);
                
                for (int i = 0; i < field.Length; i++)
                {
                    cmd.Parameters.Add(new SqlParameter("@" + field[i], obj[i]));
                }

                cmd.ExecuteNonQuery();
                ok = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                ok = false;
            }
            return ok;
        }
        // Sua 1 record su dung  SqlParameter
        public bool edit(string table, string[] fieldedit, object[] objedit, string[] fieldwhere, object[] objwhere)
        {
            bool ok = false;
            try
            {
                string sfieldedit = "";
                string sfieldwhere = "";
                for (int i = 0; i < fieldedit.Length; i++)
                {
                    sfieldedit = sfieldedit + fieldedit[i] + "=" + "@" + fieldedit[i] + ",";


                }

                sfieldedit = sfieldedit.Substring(0, sfieldedit.Length - 1);
                for (int i = 0; i < fieldwhere.Length; i++)
                {
                    sfieldwhere = sfieldwhere + fieldwhere[i] + " = @" + fieldwhere[i] + " and ";


                }

                sfieldwhere = sfieldwhere.Substring(0, sfieldwhere.Length - 5);

                string sql = "update  " + table + " set " + sfieldedit + " where " + sfieldwhere + "";
                //Ultilities.thongBao(sql);
                SqlCommand cmd = new SqlCommand(sql, DatabaseStatic.conn);
                for (int i = 0; i < fieldedit.Length; i++)
                {
                    cmd.Parameters.Add(new SqlParameter("@" + fieldedit[i], objedit[i]));
                }
                for (int i = 0; i < fieldwhere.Length; i++)
                {
                    cmd.Parameters.Add(new SqlParameter("@" + fieldwhere[i], objwhere[i]));
                }

                cmd.ExecuteNonQuery();
                ok = true;
            }
            catch (Exception)
            {

                ok = false;
            }
            return ok;
        }
        // xoa 1 record su dung  SqlParameter
        public bool delete(string table, string[] field, object[] obj)
        {
            bool ok = false;
            try
            {
                string sfield = "";

                for (int i = 0; i < field.Length; i++)
                {
                    sfield = sfield + field[i] + " = @" + field[i] + ",";

                }

                sfield = sfield.Substring(0, sfield.Length - 1);

                string sql = "delete " + table + " where " + sfield + "";
                SqlCommand cmd = new SqlCommand(sql, DatabaseStatic.conn);
                for (int i = 0; i < field.Length; i++)
                {
                    cmd.Parameters.Add(new SqlParameter("@" + field[i], obj[i]));
                }

                cmd.ExecuteNonQuery();
                ok = true;
            }
            catch (Exception)
            {

                ok = false;
            }
            return ok;
        }
        // them 1 record su dung  SqlParameter va transaction
        public bool addTrans(string table, string[] field, object[] obj)
        {
            bool ok = false;
            try
            {
                string sfield = "";
                string sfield1 = "";
                for (int i = 0; i < field.Length; i++)
                {
                    sfield = sfield + field[i] + ",";
                    sfield1 = sfield1 + "@" + field[i] + ",";

                }

                sfield = sfield.Substring(0, sfield.Length - 1);
                sfield1 = sfield1.Substring(0, sfield1.Length - 1);
                string sql = "insert into " + table + "(" + sfield + ") values (" + sfield1 + ")";
                SqlCommand cmd = DatabaseStatic.conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.Transaction = DatabaseStatic.sqltrans;
                for (int i = 0; i < field.Length; i++)
                {
                    cmd.Parameters.Add(new SqlParameter("@" + field[i], obj[i]));
                }

                cmd.ExecuteNonQuery();
                ok = true;
            }
            catch (Exception)
            {

                ok = false;
            }
            return ok;
        }
        // sua 1 record su dung  SqlParameter va transaction
        public bool editTrans(string table, string[] fieldedit, object[] objedit, string[] fieldwhere, object[] objwhere)
        {
            bool ok = false;
            try
            {
                string sfieldedit = "";
                string sfieldwhere = "";
                for (int i = 0; i < fieldedit.Length; i++)
                {
                    sfieldedit = sfieldedit + fieldedit[i] + "=" + "@" + fieldedit[i] + ",";


                }

                sfieldedit = sfieldedit.Substring(0, sfieldedit.Length - 1);
                for (int i = 0; i < fieldwhere.Length; i++)
                {
                    sfieldwhere = sfieldwhere + fieldwhere[i] + " = @" + fieldwhere[i] + " and ";


                }

                sfieldwhere = sfieldwhere.Substring(0, sfieldwhere.Length - 5);

                string sql = "update  " + table + " set " + sfieldedit + " where " + sfieldwhere + "";
                SqlCommand cmd = DatabaseStatic.conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.Transaction = DatabaseStatic.sqltrans;
                for (int i = 0; i < fieldedit.Length; i++)
                {
                    cmd.Parameters.Add(new SqlParameter("@" + fieldedit[i], objedit[i]));
                }
                for (int i = 0; i < fieldwhere.Length; i++)
                {
                    cmd.Parameters.Add(new SqlParameter("@" + fieldwhere[i], objwhere[i]));
                }

                cmd.ExecuteNonQuery();
                ok = true;
            }
            catch (Exception)
            {

                ok = false;
            }
            return ok;
        }
        
        //Thuc thi lenh sql
        public bool executeQuery(string query)
        {
            bool ok = false;
            SqlCommand cmd = new SqlCommand();
            //SqlCommand cmd = DatabaseStatic.conn.CreateCommand();
            //cmd.CommandText = query;
            //cmd.Transaction = sqltrans;
            cmd = new SqlCommand(query, DatabaseStatic.conn);
            try
            {
                cmd.ExecuteNonQuery();
                ok = true;
            }
            catch (Exception)
            {
                
                ok = false;
            }
            return ok;
        }
        // thuc thi lenh sql su dung Transaction
        public Boolean executeQueryTrans(string query)
        {
            //SqlTransaction sqltrans = DatabaseStatic.conn.BeginTransaction();
            Boolean ok = false;
            SqlCommand cmd = DatabaseStatic.conn.CreateCommand();
            cmd.CommandText = query;
            cmd.Transaction = DatabaseStatic.sqltrans;
            try
            {
                cmd.ExecuteNonQuery();
                ok = true;
            }
            catch (Exception )
            {

                ok = false;
               // MessageBox.Show(ex.ToString());
            }
            return ok;
            
        }

        //thuc hien lenh sql, ket qua tra ve la DataTable
        public DataTable getDataTable(string query)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            da = new SqlDataAdapter(query, DatabaseStatic.conn);
            DataTable dt = new DataTable();
            try
            {
                da.Fill(dt);
            }
            catch (Exception )
            {
                dt = null;
                //MessageBox.Show(ex.Message);
            }

            return dt;
        }
        //thuc thi 1 thu tuc ket qua tra ve la DataTable
        public DataTable getDataTableSP(string strSP, string[] strPara, string[] strValue)
        {
            DataTable dt = new DataTable();
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = DatabaseStatic.conn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = strSP;
                SqlParameter myPara = null;
                for (int i = 0; i < strPara.Length; i++)
                {
                    myPara = new SqlParameter(strPara[i], strValue[i]);
                    cmd.Parameters.Add(myPara);
                }
                SqlDataAdapter myData = new SqlDataAdapter(cmd);
                myData.Fill(dt);
            }
            catch (Exception)
            {
                dt = null;
            }
            return dt;
        }
        // thuc hien lenh sql tra ve DataSet
        public DataSet getDataSet(string query)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            da = new SqlDataAdapter(query, DatabaseStatic.conn);
            DataSet ds = new DataSet();
            try
            {
                da.Fill(ds);
            }
            catch (Exception)
            {
                ds = null;
                // MessageBox.Show(ex.ToString());
            }
            return ds;
        }
        // Thuc hien thu tuc tra ve DataSet
        public DataSet getDataSetSP(string strSP, string[] strPara, string[] strValue)
        {
            DataSet ds = new DataSet();
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = DatabaseStatic.conn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = strSP;
                SqlParameter myPara = null;
                for (int i = 0; i < strPara.Length; i++)
                {
                    myPara = new SqlParameter(strPara[i], strValue[i]);
                    cmd.Parameters.Add(myPara);
                }
                SqlDataAdapter myData = new SqlDataAdapter(cmd);
                myData.Fill(ds);
            }
            catch (Exception)
            {
                ds = null;
            }
            return ds;
        }
        // thuc thi 1 thu tuc
        public bool ExecuteSP(string strSP, string[] strPara, string[] strValue)
        {

            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = DatabaseStatic.conn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = strSP;
                SqlParameter myPara = null;
                for (int i = 0; i < strValue.Length; i++)
                {
                    myPara = new SqlParameter(strPara[i], strValue[i]);
                    cmd.Parameters.Add(myPara);
                }
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
        //public void Closeconnection()
        //{
        //    if (connection.State == ConnectionState.Open)
        //        connection.Close();


        //    connection.Dispose();
        //}
        public string getString(string query)
        {
            string str = "";
            DataTable dt = new DataTable();
            dt = getDataTable(query);
            if (dt != null)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    str = dr[0].ToString();
                    //break;
                }
            }
            return str;
        }
        public double getNumberValue(string query)
        {
            double value = 0;
            DataTable dt = new DataTable();
            dt = getDataTable(query);
            if (dt != null)
            {
                try
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        value = double.Parse(dr[0].ToString());

                    }
                }
                catch (Exception)
                {
                    value = 0;
                    
                }
               
            }
            return value;
        }
        public bool isExist(string query)
        {

            DataTable dt = new DataTable();
            dt = getDataTable(query);
            if (dt.Rows.Count > 0)
            {
                return true;
            }
            return false;
        }
        public Boolean excuteQueryTrans2(string query1, string query2)
        {

            SqlTransaction sqltrans = DatabaseStatic.conn.BeginTransaction();
            try
            {


                SqlCommand cmdsql1 = DatabaseStatic.conn.CreateCommand();

                cmdsql1.CommandText = query1;
                cmdsql1.Transaction = sqltrans;
                cmdsql1.ExecuteNonQuery();
                SqlCommand cmdsql2 = DatabaseStatic.conn.CreateCommand();

                cmdsql2.CommandText = query2;
                cmdsql2.Transaction = sqltrans;
                cmdsql2.ExecuteNonQuery();

                sqltrans.Commit();

            }
            catch (Exception)
            {

                sqltrans.Rollback();
                return false;
            }
            return true;

        }
        public Boolean excuteQueryTrans3(string query1, string query2, string query3)
        {

            SqlTransaction sqltrans = DatabaseStatic.conn.BeginTransaction();
            try
            {


                SqlCommand cmdsql1 = DatabaseStatic.conn.CreateCommand();

                cmdsql1.CommandText = query1;
                cmdsql1.Transaction = sqltrans;
                cmdsql1.ExecuteNonQuery();
                SqlCommand cmdsql2 = DatabaseStatic.conn.CreateCommand();

                cmdsql2.CommandText = query2;
                cmdsql2.Transaction = sqltrans;
                cmdsql2.ExecuteNonQuery();
                SqlCommand cmdsql3 = DatabaseStatic.conn.CreateCommand();
                cmdsql3.CommandText = query3;
                cmdsql3.Transaction = sqltrans;
                cmdsql3.ExecuteNonQuery();
                sqltrans.Commit();

            }
            catch (Exception)
            {

                sqltrans.Rollback();
                return false;
            }
            return true;

        }
    }
}
